Displaying Sql Queries In Excel

Task/Problem Overview

With Microsoft Excel, you have the ability to pull information from Savance Enterprise using an SQL query. This functionality will help you gather and organize information in an orderly manner. For starters, you will have to create a stored procedure that gathers the desired information. Then you will have to create an account that can execute the stored procedure. Once the stored procedure is executed, you will be able to display the results in Excel.

Creating an SQL User Account

• Login to SQL Server Management Studio

• Drop down the Security folder

• Right-click Logins

• Select New Login…

  

• Input your user or group into the login name box

• Input your user or group into the login name box

  • Click the Search... option

  • Click Object Types...

  • Check the box next to Groups

    

  • Select OK

  • Type the user or group in the box

  • Click Check Names to verify the name

    

• Make sure you are using Windows Authentication if you do not want users to be prompted for credentials

• Change the default database to the desired database

• Click the User Mapping option in the left pane

• Check the box next to your Database

• Give the desired database db_datareader and db_denydatawriter roles

• Check the box next to the other Databases

• Give the other Databases db_denydatareader and db_denydatawriter roles

• Select OK

Giving Permissions to SQL User:

A query needs to be executed on the database to grant permission to the user. Right-click the database and select the New Query option. In the space that is provided, execute the following statement:

• GRANT EXECUTE ON [dbo].[StoredProcedureName] TO [NewLoginName]

"StoredProcedureName" and "NewLoginName" need to be changed to your information.

Displaying Data in Excel

Follow the steps below to gather information from the SQL server using Excel:

• Click the Data tab

• Click Get Data > From Database > From SQL Server

  

• Type in your server name

• Use Windows Authentication or type in your credentials

• Select the database and any table

• Click Finish

• Click Properties

• Go to the Definition tab

• Change Command type to SQL

• Change the table name to the Stored Procedure name

  

• Click OK on the warning message.

• Click OK on the Import Data window

Now you should see the data in the Excel cells. Every time you refresh the page, the data should refresh.